#include "cadreportrait.h"
#include "ui_cadreportrait.h"
#include <QDebug>
#include <QSqlQuery>

CadrePortrait::CadrePortrait(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::CadrePortrait)
{
    ui->setupUi(this);
    initTableWidget();
    showCadreBaseInfo();
}

CadrePortrait::~CadrePortrait()
{
    delete ui;
}

//初始化tableWidget
void CadrePortrait::initTableWidget()
{
    ui->tableWidget_gb->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableWidget_gb->setSelectionBehavior(QAbstractItemView::SelectRows);  //整行选中的方式
    ui->tableWidget_gb->setSelectionMode(QAbstractItemView::SingleSelection);  //设置为可以选中单个
    ui->tableWidget_gb->setStyleSheet("selection-background-color:rgb(173,216,230);"); //设置选中行的背景色
    ui->tableWidget_sj->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableWidget_kh->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableWidget_khc->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableWidget_px->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableWidget_jl->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableWidget_cc->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableWidget_qs->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableWidget_lx->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableWidget_xy->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableWidget_tx->setEditTriggers(QAbstractItemView::NoEditTriggers);
}

//显示干部信息
void CadrePortrait::showCadreBaseInfo()
{
    ui->tableWidget_gb->setRowCount(0);
    QSqlQuery query;
    query.exec("select CadreName,Sex,IDNum,NativePlace,UnitID,RankID,RankTime from CadreInfo");
    while(query.next()) //一行一行遍历
    {
        int rowc = ui->tableWidget_gb->rowCount();//获取行数
        ui->tableWidget_gb->insertRow(rowc);
        //干部名称
        ui->tableWidget_gb->setItem(rowc,0,new QTableWidgetItem(query.value(0).toString()));
        //Sex
        if(query.value(1).toInt()== 1)
            ui->tableWidget_gb->setItem(rowc,1,new QTableWidgetItem("男"));
        if(query.value(1).toInt()== 0)
            ui->tableWidget_gb->setItem(rowc,1,new QTableWidgetItem("女"));
        //证件号码
        ui->tableWidget_gb->setItem(rowc,2,new QTableWidgetItem(query.value(2).toString()));
        //籍贯
        QString sqlAdminArea = QString("select AdminArea from AdminAreaCode where AdminAreaID = '%1'").arg(query.value(3).toString());
        QSqlQuery queryAdminArea;
        queryAdminArea.exec(sqlAdminArea);
        while(queryAdminArea.next()) //一行一行遍历
        {
            ui->tableWidget_gb->setItem(rowc,3,new QTableWidgetItem(queryAdminArea.value(0).toString()));
        }
        //学历(最高)
        QString sqlEduNum = QString("select EduNum from Edu where  IDNum = '%1'").arg(query.value(2).toString());
        QSqlQuery queryEduNum;
        queryEduNum.exec(sqlEduNum);
        int eduMaxCode=100;
        while(queryEduNum.next()) //一行一行遍历
        {
            if(queryEduNum.value(0).toInt() < eduMaxCode) eduMaxCode = queryEduNum.value(0).toInt();
        }
        QString sqlEduName = QString("select Education from EducationCode where  EducationID = '%1'").arg(eduMaxCode);
        QSqlQuery queryEduName;
        queryEduName.exec(sqlEduName);
        while(queryEduName.next()) //一行一行遍历
        {
            ui->tableWidget_gb->setItem(rowc,4,new QTableWidgetItem(queryEduName.value(0).toString()));
        }
        //单位
        QString sqlUnit = QString("select Unit from UnitCode where UnitID = '%1'").arg(query.value(4).toString());
        QSqlQuery queryUnit;
        queryUnit.exec(sqlUnit);
        while(queryUnit.next()) //一行一行遍历
        {
            ui->tableWidget_gb->setItem(rowc,5,new QTableWidgetItem(queryUnit.value(0).toString()));
        }
        //职级
        QString rankCode = query.value(5).toString();
        QString sql = QString("select RankName from RankCode where RankID = '%1'").arg(rankCode);
        QSqlQuery rankQuery;
        rankQuery.exec(sql);
        QString rankName;
        while(rankQuery.next()) //一行一行遍历
        {
            rankName = rankQuery.value(0).toString();
        }
        ui->tableWidget_gb->setItem(rowc,6,new QTableWidgetItem(rankName));
        //级别时间
        ui->tableWidget_gb->setItem(rowc,7,new QTableWidgetItem(query.value(6).toString()));
    }
    int rows = ui->tableWidget_gb->rowCount();
    QString newtext = QString(" 共: %1 条").arg(rows);
    ui->label_4->setText(newtext);
}

//上方干部信息行查询
void CadrePortrait::on_buttonFind_clicked()
{
    int rows = ui->tableWidget_gb->rowCount();
    QString gbName = ui->lineEdit->text();
    if ("" == gbName)
    {
        showCadreBaseInfo();
    }
    else
    {
        QList <QTableWidgetItem *> item = ui->tableWidget_gb->findItems(gbName, Qt::MatchContains);

        for (int i = 0; i < rows; i++)
        {
            ui->tableWidget_gb->setRowHidden(i, true);//隐藏所有行
        }

        if (!item.isEmpty())
        {
            for (int i = 0; i < item.count(); i++)
            {
                ui->tableWidget_gb->setRowHidden(item.at(i)->row(),false);//item.at(i).row()输出行号
            }
            QString newtext = QString(" 共: %1 条").arg(item.count());
            ui->label_4->setText(newtext);
        }
    }
}

//选择干部行显示对应其他tableWidget信息
void CadrePortrait::on_tableWidget_gb_itemSelectionChanged()
{
    int currentRow = ui->tableWidget_gb->currentRow();
    if (currentRow != -1)//如果选中了一行
    {
        //更新当前干部:
        if(ui->tableWidget_gb->item(currentRow,0)!=NULL){
            QString gbName = ui->tableWidget_gb->item(currentRow,0)->text();
            QString newCadreName = QString("当前干部:  %1").arg(gbName);
            ui->label_3->setText(newCadreName);
        }

        //考核部分
        ui->tableWidget_kh->setRowCount(0);
        QString gbIDNum;
        if(ui->tableWidget_gb->item(currentRow,2)!=NULL){
            gbIDNum = ui->tableWidget_gb->item(currentRow,2)->text();
        }
        QString check = QString("select CheckYear,CheckResult from `Check` where IDNum = %1").arg(gbIDNum);
        QSqlQuery queryCheck;
        queryCheck.exec(check);
        while(queryCheck.next()) //一行一行遍历
        {
            int rowc = ui->tableWidget_kh->rowCount();//获取行数
            ui->tableWidget_kh->insertRow(rowc);
            for(int i = 0 ; i < 2 ; i++ )
            {
                ui->tableWidget_kh->setItem(rowc,i,new QTableWidgetItem(queryCheck.value(i).toString()));
            }

        }
        int rows_kh = ui->tableWidget_kh->rowCount();//获取行数
        QString khTitle = QString("考核情况（%1）").arg(rows_kh);
        ui->groupBox_kh->setTitle(khTitle);

        //考核情况统计
        int excellent=0;
        int competent=0;
        int basicCompetence=0;
        int indefinite=0;
        int rowc = ui->tableWidget_kh->rowCount();
        for(int i = 0 ;i < rowc ;i++)
        {
            if(ui->tableWidget_kh->item(i,1)->text() == "优秀") excellent++;
            if(ui->tableWidget_kh->item(i,1)->text() == "称职") competent++;
            if(ui->tableWidget_kh->item(i,1)->text() == "基本称职") basicCompetence++;
            if(ui->tableWidget_kh->item(i,1)->text() == "不定等次") indefinite++;
            //qDebug()<<i;
        }
        ui->tableWidget_khc->setRowCount(0);
        ui->tableWidget_khc->insertRow(0);
        ui->tableWidget_khc->setItem(0,0,new QTableWidgetItem(QString::number(excellent)));
        ui->tableWidget_khc->setItem(0,1,new QTableWidgetItem(QString::number(competent)));
        ui->tableWidget_khc->setItem(0,2,new QTableWidgetItem(QString::number(basicCompetence)));
        ui->tableWidget_khc->setItem(0,3,new QTableWidgetItem(QString::number(indefinite)));
        //qDebug()<<excellent<<competent<<basicCompetence<<indefinite;

        //培训部分
        ui->tableWidget_px->setRowCount(0);
        QString train = QString("select StartTime,EndTime,TrainType,TrainOrgan,TrainContent from Train where IDNum = %1").arg(gbIDNum);
        QSqlQuery queryTrain;
        queryTrain.exec(train);
        while(queryTrain.next()) //一行一行遍历
        {
            int rowc = ui->tableWidget_px->rowCount();//获取行数
            ui->tableWidget_px->insertRow(rowc);
            for(int i = 0 ; i < 5 ; i++ )
            {
                ui->tableWidget_px->setItem(rowc,i,new QTableWidgetItem(queryTrain.value(i).toString()));
            }

        }
        int rows_px = ui->tableWidget_px->rowCount();//获取行数
        QString pxTitle = QString("培训情况（%1）").arg(rows_px);
        ui->groupBox_px->setTitle(pxTitle);

        //奖励部分
        ui->tableWidget_jl->setRowCount(0);
        QString reward = QString("select RewardTime,RewardType,RewardName from Reward where IDNum = %1").arg(gbIDNum);
        QSqlQuery queryReward;
        queryReward.exec(reward);
        while(queryReward.next()) //一行一行遍历
        {
            int rowc = ui->tableWidget_jl->rowCount();//获取行数
            ui->tableWidget_jl->insertRow(rowc);
            for(int i = 0 ; i < 3 ; i++ )
            {
                ui->tableWidget_jl->setItem(rowc,i,new QTableWidgetItem(queryReward.value(i).toString()));
            }

        }
        int rows_jl = ui->tableWidget_jl->rowCount();//获取行数
        QString jlTitle = QString("奖励情况（%1）").arg(rows_jl);
        ui->groupBox_jl->setTitle(jlTitle);

        //惩处部分
        ui->tableWidget_cc->setRowCount(0);
        QString punish = QString("select PunishTime,PunishTypeID,PunishName,PunishReason from Punish where IDNum = %1").arg(gbIDNum);
        QSqlQuery queryPunish;
        queryPunish.exec(punish);
        while(queryPunish.next()) //一行一行遍历
        {
            int rowc = ui->tableWidget_cc->rowCount();//获取行数
            ui->tableWidget_cc->insertRow(rowc);

            ui->tableWidget_cc->setItem(rowc,0,new QTableWidgetItem(queryPunish.value(0).toString()));
            int punishType = queryPunish.value(1).toInt();
            //qDebug()<<punishType;
            QString punishSql = QString("select PunishType from PunishTypeContrast where PunishTypeID ='%1'").arg(punishType);
            QSqlQuery punishQuery;
            punishQuery.exec(punishSql);
            QString punishResult;
            while(punishQuery.next()) //一行一行遍历
            {
                punishResult = punishQuery.value(0).toString();
                //qDebug()<<punishResult;
            }
            ui->tableWidget_cc->setItem(rowc,1,new QTableWidgetItem(punishResult));
            ui->tableWidget_cc->setItem(rowc,2,new QTableWidgetItem(queryPunish.value(2).toString()));
            ui->tableWidget_cc->setItem(rowc,3,new QTableWidgetItem(queryPunish.value(3).toString()));

        }
        int rows_cc = ui->tableWidget_cc->rowCount();//获取行数
        QString ccTitle = QString("惩处情况（%1）").arg(rows_cc);
        ui->groupBox_cc->setTitle(ccTitle);

        ui->tableWidget_qs->setRowCount(0);
        ui->tableWidget_lx->setRowCount(0);
        ui->tableWidget_xy->setRowCount(0);
        ui->tableWidget_tx->setRowCount(0);
        social_relation();
    }
}

//显示同单位社交关系
void CadrePortrait::social_relation()
{
    int relation=0;//亲属人数
    int sameplace=0;//老乡人数
    int schoolmate=0;//校友人数
    int classmate=0;//同学人数

    //当前选定干部
    int currentRow = ui->tableWidget_gb->currentRow();
    QString gbIDNum;
    if(ui->tableWidget_gb->item(currentRow,2)->text()!=NULL)
    {
        gbIDNum = ui->tableWidget_gb->item(currentRow,2)->text();
    }

    QSqlQuery query;

    //选定干部单位
    QString unitId;
    query.exec("select UnitID from CadreInfo where IDNum='"+gbIDNum+"'");
    while (query.next())
    {
        unitId = query.value(0).toString();      
    }

    //亲属
    QString relationId;
    QString politicId;
    QString unitName;
    QString sqlFamily = QString("select Name,BirthTime,RelationID,PoliticID,UnitID,Profession,FamilyNum from Family where IDNum='%1' and UnitID='%2'").arg(gbIDNum).arg(unitId);
    QSqlQuery familyPeoQuery(sqlFamily);
    while (familyPeoQuery.next())
    {
        relation++;
        int rowc = ui->tableWidget_qs->rowCount();//获取行数
        ui->tableWidget_qs->insertRow(rowc);
        for(int i=0;i<2;i++)
        {
            ui->tableWidget_qs->setItem(rowc,i,new QTableWidgetItem(familyPeoQuery.value(i).toString()));
        }
        //关系
        relationId = familyPeoQuery.value(2).toString();
        QSqlQuery queryRelation;
        queryRelation.exec(QString("select Relation from RelationCode where RelationID = '%1'").arg(relationId));
        while(queryRelation.next())
        {
            ui->tableWidget_qs->setItem(rowc,2,new QTableWidgetItem(queryRelation.value(0).toString()));
        }
        //政治面貌
        politicId = familyPeoQuery.value(3).toString();
        QSqlQuery queryPolitic;
        queryPolitic.exec(QString("select Politic from PoliticCode where PoliticID = '%1'").arg(politicId));
        while(queryPolitic.next())
        {
            ui->tableWidget_qs->setItem(rowc,3,new QTableWidgetItem(queryPolitic.value(0).toString()));
        }
        //单位名称
        QString sqlUnit = QString("select Unit from UnitCode where UnitID = '%1'").arg(unitId);
        QSqlQuery queryUnit;
        queryUnit.exec(sqlUnit);
        while(queryUnit.next()) //一行一行遍历
        {
            unitName = queryUnit.value(0).toString();
            ui->tableWidget_qs->setItem(rowc,4,new QTableWidgetItem(unitName));
        }

        for(int j=5;j<7;j++)
        {
            ui->tableWidget_qs->setItem(rowc,j,new QTableWidgetItem(familyPeoQuery.value(j).toString()));
        }
    }

    //老乡
    QString native;
    query.exec("select NativePlace from CadreInfo where IDNum='"+gbIDNum+"'");
    while (query.next()) {
        //籍贯
        native = query.value(0).toString();
        //qDebug()<<native;
    }
    QSqlQuery qu3("select CadreName,IDNum,Sex,Birth,PoliticID,RankID from CadreInfo where NativePlace='"+native+"' and UnitID='"+unitId+"'and IDNum!='"+gbIDNum+"'");
    while (qu3.next())
    {
        sameplace++;
        int rowc = ui->tableWidget_lx->rowCount();//获取行数
        ui->tableWidget_lx->insertRow(rowc);
        //姓名、证件号、性别、出生时间
        for(int i=0;i<2;i++)
        {
            ui->tableWidget_lx->setItem(rowc,i,new QTableWidgetItem(qu3.value(i).toString()));
        }
        if(qu3.value(2).toString()== "1")
            ui->tableWidget_lx->setItem(rowc,2,new QTableWidgetItem("男"));
        if(qu3.value(2).toString()== "0")
            ui->tableWidget_lx->setItem(rowc,2,new QTableWidgetItem("女"));
        ui->tableWidget_lx->setItem(rowc,3,new QTableWidgetItem(qu3.value(3).toString()));
        //政治面貌
        QString politicCode = qu3.value(4).toString();
        QSqlQuery queryPolitic;
        queryPolitic.exec(QString("select Politic from PoliticCode where PoliticID = '%1'").arg(politicCode));
        while(queryPolitic.next())
        {
            ui->tableWidget_lx->setItem(rowc,4,new QTableWidgetItem(queryPolitic.value(0).toString()));
        }
        //职级
        QString rankCode = qu3.value(5).toString();
        QString sql = QString("select RankName from RankCode where RankID = '%1'").arg(rankCode);
        QSqlQuery rankQuery;
        rankQuery.exec(sql);
        while(rankQuery.next()) //一行一行遍历
        {
            ui->tableWidget_lx->setItem(rowc,5,new QTableWidgetItem(rankQuery.value(0).toString()));
        }
    }

    QSqlQuery sameUnitPeo("select IDNum from CadreInfo where UnitID='"+unitId+"' and IDNum!='"+gbIDNum+"'");
    while (sameUnitPeo.next())
    {
        QString samePeoID = sameUnitPeo.value(0).toString(); //同单位人员证件号
        //学校、专业、入学时间
        QString school;
        QString major;
        QString stime;
        query.exec("select School,Major,StartTime from Edu where IDNum='"+gbIDNum+"'");
        while (query.next())
        {
            //学校
            school = query.value(0).toString();
            //专业
            major = query.value(1).toString();
            //入学时间
            stime = query.value(2).toString();

            //校友（学校相同）
            QSqlQuery qu("select School from Edu where School='"+school+"' and IDNum='"+samePeoID+"'");
            while (qu.next())
            {
                //schoolmate++;
                QString sqlSchoolFriend = QString("select CadreName,IDNum,Sex,Birth,PoliticID,RankID from CadreInfo where IDNum='%1'").arg(samePeoID);
                QSqlQuery schoolFriendQuery(sqlSchoolFriend);
                while (schoolFriendQuery.next())
                {
                    int rowc = ui->tableWidget_xy->rowCount();//获取行数
                    ui->tableWidget_xy->insertRow(rowc);
                    schoolmate++;
                    //姓名、证件号、性别、出生时间
                    for(int i=0;i<2;i++)
                    {
                        ui->tableWidget_xy->setItem(rowc,i,new QTableWidgetItem(schoolFriendQuery.value(i).toString()));
                    }
                    if(schoolFriendQuery.value(2).toString()== "1")
                        ui->tableWidget_xy->setItem(rowc,2,new QTableWidgetItem("男"));
                    if(schoolFriendQuery.value(2).toString()== "0")
                        ui->tableWidget_xy->setItem(rowc,2,new QTableWidgetItem("女"));
                    ui->tableWidget_xy->setItem(rowc,3,new QTableWidgetItem(schoolFriendQuery.value(3).toString()));
                    //政治面貌
                    QString politicCode = schoolFriendQuery.value(4).toString();
                    QSqlQuery queryPolitic;
                    queryPolitic.exec(QString("select Politic from PoliticCode where PoliticID = '%1'").arg(politicCode));
                    while(queryPolitic.next())
                    {
                        ui->tableWidget_xy->setItem(rowc,4,new QTableWidgetItem(queryPolitic.value(0).toString()));
                    }
                    //职级
                    QString rankCode = schoolFriendQuery.value(5).toString();
                    QString sql = QString("select RankName from RankCode where RankID = '%1'").arg(rankCode);
                    QSqlQuery rankQuery;
                    rankQuery.exec(sql);
                    while(rankQuery.next()) //一行一行遍历
                    {
                        ui->tableWidget_xy->setItem(rowc,5,new QTableWidgetItem(rankQuery.value(0).toString()));
                    }
                }
             }

            //同学（学校、专业、入学时间均相同）
            QSqlQuery qu2("select EduID from Edu where School='"+school+"' and Major='"+major+"' and StartTime='"+stime+"'and IDNum='"+samePeoID+"'");
            while (qu2.next())
            {
                //classmate++;
                QString sqlClassFriend = QString("select CadreName,IDNum,Sex,Birth,PoliticID,RankID from CadreInfo where IDNum='%1'").arg(samePeoID);
                QSqlQuery classFriendQuery(sqlClassFriend);
                while (classFriendQuery.next())
                {
                    int rowc = ui->tableWidget_tx->rowCount();//获取行数
                    ui->tableWidget_tx->insertRow(rowc);
                    classmate++;
                    //姓名、证件号、性别、出生时间
                    for(int i=0;i<2;i++)
                    {
                        ui->tableWidget_tx->setItem(rowc,i,new QTableWidgetItem(classFriendQuery.value(i).toString()));
                    }
                    if(classFriendQuery.value(2).toString()== "1")
                        ui->tableWidget_tx->setItem(rowc,2,new QTableWidgetItem("男"));
                    if(classFriendQuery.value(2).toString()== "0")
                        ui->tableWidget_tx->setItem(rowc,2,new QTableWidgetItem("女"));
                    ui->tableWidget_tx->setItem(rowc,3,new QTableWidgetItem(classFriendQuery.value(3).toString()));
                    //政治面貌
                    QString politicCode = classFriendQuery.value(4).toString();
                    QSqlQuery queryPolitic;
                    queryPolitic.exec(QString("select Politic from PoliticCode where PoliticID = '%1'").arg(politicCode));
                    while(queryPolitic.next())
                    {
                        ui->tableWidget_tx->setItem(rowc,4,new QTableWidgetItem(queryPolitic.value(0).toString()));
                    }
                    //职级
                    QString rankCode = classFriendQuery.value(5).toString();
                    QString sql = QString("select RankName from RankCode where RankID = '%1'").arg(rankCode);
                    QSqlQuery rankQuery;
                    rankQuery.exec(sql);
                    while(rankQuery.next()) //一行一行遍历
                    {
                        ui->tableWidget_tx->setItem(rowc,5,new QTableWidgetItem(rankQuery.value(0).toString()));
                    }
                }
            }

        }
    }

    //显示统计人数
    ui->tableWidget_sj->setRowCount(0);
    ui->tableWidget_sj->insertRow(0);
    ui->tableWidget_sj->setItem(0,0,new QTableWidgetItem(QString::number(relation)));
    ui->tableWidget_sj->setItem(0,1,new QTableWidgetItem(QString::number(sameplace)));
    ui->tableWidget_sj->setItem(0,2,new QTableWidgetItem(QString::number(schoolmate)));
    ui->tableWidget_sj->setItem(0,3,new QTableWidgetItem(QString::number(classmate)));

    /*QSqlQuery sameUnitPeo("select IDNum from CadreInfo where UnitID='"+unitid+"' and IDNum!='"+gbIDNum+"'");
    while (sameUnitPeo.next()) {
        QString samePeoID=sameUnitPeo.value(0).toString();//同单位人证件号
        //亲属且同单位
        query.exec("select Name,BirthTime,Relation,Politic,UnitName,Profession,FamilyNum from Family where IDNum='"+gbIDNum+"' and FamilyNum='"+samePeoID+"'");
        //存在同单位亲属
        if(query.next()) {
            //QString famNum=query.value(0).toString();
            //只一条数据或无
            relation++;

        }
        //不存在则检查是否漏添
        else{
            QSqlQuery check("select FamilyNum from Family where IDNum='"+samePeoID+"' and FamilyNum='"+gbIDNum+"'");
            if(check.next()){
                relation++;
            }
        }
        ui->tableWidget_sj->setItem(0,0,new QTableWidgetItem(QString::number(relation)));*/

}
